package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import auxilary.Constants;
import auxilary.Log;
import entities.Course;
import entities.Session;
import entities.User;
import exceptions.CourseException;
import exceptions.DBException;
import exceptions.MyException;
import exceptions.UserException;

/* ******************************************************** */
/* 						CourseHandler						*/
/*															*/
/*	Takes care of communicating with the database on		*/
/* 	behalf of courses										*/
/*															*/
/* ******************************************************** */

public class CourseHandler extends DBHandler {

	/*
	 * Add Course - add the course to the DB
	 */
	public int addCourse(Course course) throws MyException {
		// check course fields
		if (course == null || course.getName() == null
				|| course.getName().isEmpty())
			throw new MyException(UserException.BAD_PARAMS);

		/*
		 * the next segment of code is syncronized by locking the appropriate
		 * sql query rows so as not to hurt concurency
		 */
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (Exception e) {
			Log.err("Cant get connection");
			return 0;
		}
		try {
			conn.setTransactionIsolation(1);
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			Log.err("Couldn't lock, exited...");
			return 0;
		}

		int id = 0;
		try {
			// check sessions
			if (!SessionsAreAvailable(course.getSessions(), course, conn)) {
				return -1;
			}

			// add course to database
			Object[][] data = this.createAddCourseData(course);
			id = super.insert(data, Tables.SCHEMA + "." + Tables.Course.dbName,
					conn);
			course.setId(id);
			SessionHandler sh = new SessionHandler();
			for (Session session : course.getSessions()) {
				sh.addSession(session, course, conn);
			}
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				Log.err("failed to rollback, we are all doomed");
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		TagHandler th = new TagHandler();
		String[] tags = course.getName().toLowerCase().split(" ");
		th.addTagsToCourse(tags, id);

		return id;
	}

	/*
	 * Fetch Course - get course with the id from the DB
	 */
	public Course fetchCourse(int id, Connection conn) throws MyException,
			SQLException {
		// check id
		if (id < 0)
			throw new MyException(UserException.BAD_PARAMS);

		// get the user from the DB.
		String where = Tables.Course.Columns.ID + "=" + id;
		Course course = this.doFetchCourse(where, conn);
		if (course != null) {
			course.setTaken(getTakenSpots(course.getId(), conn));
		}
		return course;
	}

	/*
	 * Fetch Course - get course with name from the DB
	 */
	public Course fetchCourse(String name) throws MyException {
		// check course name
		if (name == null || name.isEmpty())
			throw new MyException(CourseException.BAD_PARAMS);

		// get the user from the DB.
		String where = Tables.Course.Columns.NAME + "=" + "\"" + name + "\"";

		Course course = null;
		try {
			course = this.doFetchCourse(where, null);
		} catch (SQLException e) {
			Log.err("didn't create course");
		}
		if (course != null) {
			try {
				course.setTaken(getTakenSpots(course.getId(), null));
			} catch (SQLException e) {
				Log.err("didn't find taken spots for course");
			}
		}
		return course;
	}

	/*
	 * Fetch All Courses - get all courses from the DB
	 */
	public List<Course> fetchAllCourses() throws MyException {
		ResultSet rs = null;
		List<Course> courses = null;
		try {
			rs = getCourseResultSet(null, null);
			courses = buildCoursesList(rs);
		} catch (SQLException e) {
			Log.err("couldn't fetch all courses");
		} finally {
			closeConnection(rs);
		}
		return courses;
	}

	/*
	 * Fetch All Courses - get all courses from the DB with specific creatorId
	 */
	public List<Course> fetchAllCourses(int creatorId) throws MyException {
		// get the user from the DB.
		ResultSet rs = null;
		List<Course> courses = null;
		String where = Tables.Course.Columns.CREATOR_ID + "=" + creatorId;
		try {
			rs = getCourseResultSet(where, null);
			courses = buildCoursesList(rs);
		} catch (Exception e) {
			return null;
		} finally {
			closeConnection(rs);
		}
		return courses;
	}

	/*
	 * Fetch All Available Courses - get all courses from the DB with specific
	 * creatorId
	 */
	public List<Course> fetchAllNotFullCourses() throws MyException {
		ResultSet rs = null;
		List<Course> courses = null;

		// create query
		String query = "SELECT * FROM " + Tables.Course.fullDBName + " WHERE "
				+ Tables.Course.fullDBName + ".`" + Tables.Course.Columns.ID
				+ "` NOT IN (" + Constants.Queries.FULL_IDS + ")";
		Connection conn = getConnection();
		try {
			rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(query);
			courses = buildCoursesList(rs);
		} catch (Exception e) {
		} finally {
			closeConnection(rs);
		}
		return courses;
	}
	/*
	 * Fetch All Courses - get all courses with user with id from the DB
	 */
	public List<Course> fetchAllCourses(User user, boolean registered)
			throws MyException {
		ResultSet rs = null;
		List<Course> courses = null;

		// reg - string of registered
		String reg = (registered) ? "1" : "0";

		// create query
		String joinQuery = "SELECT * FROM `" + Tables.SCHEMA + "`.`"
				+ Tables.Course.dbName + "`,`" + Tables.SCHEMA + "`.`"
				+ Tables.User.dbName + "`,`" + Tables.SCHEMA + "`.`"
				+ Tables.CourseToUser.dbName + "` WHERE `" + Tables.SCHEMA
				+ "`.`" + Tables.Course.dbName + "`.`"
				+ Tables.Course.Columns.ID + "`=`" + Tables.SCHEMA + "`.`"
				+ Tables.CourseToUser.dbName + "`.`"
				+ Tables.CourseToUser.Columns.COURSE_ID + "` AND `"
				+ Tables.SCHEMA + "`.`" + Tables.User.dbName + "`.`"
				+ Tables.User.Columns.ID + "`=`" + Tables.SCHEMA + "`.`"
				+ Tables.CourseToUser.dbName + "`.`"
				+ Tables.CourseToUser.Columns.USER_ID + "` AND `"
				+ Tables.SCHEMA + "`.`" + Tables.CourseToUser.dbName + "`.`"
				+ Tables.CourseToUser.Columns.USER_ID + "`="
				+ String.valueOf(user.getId()) + " AND `" + Tables.SCHEMA
				+ "`.`" + Tables.CourseToUser.dbName + "`.`"
				+ Tables.CourseToUser.Columns.IS_REGISTERED + "`=" + reg;
		Connection conn = getConnection();
		try {
			rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(joinQuery);
			courses = buildCoursesList(rs);
		} catch (Exception e) {
		} finally {
			closeConnection(rs);
		}
		return courses;
	}

	/*
	 * Fetch All Courses - get all courses with user with id from the DB
	 */
	public List<Course> fetchAllRegisteredCoursesWithSessions(User user)
			throws MyException {
		List<Course> courses = fetchAllCourses(user, true);
		SessionHandler sh = new SessionHandler();
		for (Course course : courses) {
			try {
				course.setSessions(sh.fetchAllCourseSessions(course, null));
			} catch (SQLException e) {
			}
		}
		return courses;
	}

	/*
	 * Fetch Courses By Tags - get course by tag list
	 */
	public List<Course> fetchCoursesByTags(String[] tags, String lastTag,
			int credits, int sign, int group, boolean withFull,
			boolean onlyAvailable, User user) throws MyException {
		List<Course> courses = new LinkedList<Course>();

		// create condition
		String condition = "";
		if (credits > -1) {

			String signString;
			switch (sign) {
				case 1 :
					signString = "<=";
					break;
				case 2 :
					signString = ">=";
					break;
				default :
					signString = "=";
					break;
			}
			condition += "(" + Tables.Course.fullDBName + ".`"
					+ Tables.Course.Columns.CREDITS + "`" + signString
					+ credits + ")";
		}
		if (group != -1) {
			if (!condition.equals("")) {
				condition += " AND ";
			}
			condition += "(" + Tables.Course.fullDBName + ".`"
					+ Tables.Course.Columns.GROUP_ID + "`="
					+ String.valueOf(group) + ")";

		}
		if (!condition.equals("")) {
			condition = " AND " + condition;
		}

		// create condition
		String tagsString = "";
		String where = "";
		String numString = "";
		if (tags != null) {
			tagsString = "(";
			boolean first = true;
			for (int i = 0; i < tags.length; i++) {
				if (!first) {
					tagsString += ",";
				} else {
					first = false;
				}
				tagsString += "'" + tags[i] + "'";
			}
			tagsString += ") ";
			numString = String.valueOf(tags.length);
		}
		where += tagsString;
		// create join query
		String lastWordSelect = "SELECT " + Tables.Course.fullDBName + ".`"
				+ Tables.Course.Columns.ID + "` FROM "
				+ Tables.Course.fullDBName + ", " + Tables.Tags.fullDBName
				+ ", " + Tables.CourseToTag.fullDBName + " WHERE ("
				+ Tables.Tags.fullDBName + ".`" + Tables.Tags.Columns.NAME
				+ "` LIKE '" + lastTag + "%') AND (" + Tables.Tags.fullDBName
				+ ".`" + Tables.Tags.Columns.ID + "`=`"
				+ Tables.CourseToTag.Columns.TAG_ID + "`) AND ("
				+ Tables.Course.fullDBName + ".`" + Tables.Course.Columns.ID
				+ "`=`" + Tables.CourseToTag.Columns.COURSE_ID + "`)";
		String multipleWordSelect = "SELECT " + Tables.Course.fullDBName + ".`"
				+ Tables.Course.Columns.ID + "`, COUNT(*) as `c` FROM "
				+ Tables.Course.fullDBName + ", " + Tables.Tags.fullDBName
				+ ", " + Tables.CourseToTag.fullDBName + " WHERE ("
				+ Tables.Tags.fullDBName + ".`" + Tables.Tags.Columns.NAME
				+ "` IN " + where + ") AND (" + Tables.Course.fullDBName + ".`"
				+ Tables.Course.Columns.ID + "`="
				+ Tables.CourseToTag.fullDBName + ".`"
				+ Tables.CourseToTag.Columns.COURSE_ID + "`) AND ("
				+ Tables.Tags.fullDBName + ".`" + Tables.Tags.Columns.ID + "`="
				+ Tables.CourseToTag.fullDBName + ".`"
				+ Tables.CourseToTag.Columns.TAG_ID + "`) AND ("
				+ Tables.Course.fullDBName + ".`" + Tables.Course.Columns.ID
				+ "` IN (" + lastWordSelect + ")) GROUP BY "
				+ Tables.Course.fullDBName + ".`" + Tables.Course.Columns.ID
				+ "` HAVING `c`=" + numString;
		String innerSelect = (tags == null)
				? lastWordSelect
				: multipleWordSelect;
		String joinQuery = "SELECT * FROM " + Tables.Course.fullDBName + ", ("
				+ innerSelect + ") AS `final` WHERE `final`.`"
				+ Tables.Course.Columns.ID + "` = " + Tables.Course.fullDBName
				+ ".`id`" + condition;
		if (!withFull) {
			joinQuery += " AND " + Tables.Course.fullDBName + ".`"
					+ Tables.Course.Columns.ID + "` NOT IN ("
					+ Constants.Queries.FULL_IDS + ")";
		}
		joinQuery += " GROUP BY " + Tables.Course.fullDBName + ".`"
				+ Tables.Course.Columns.ID + "`";

		// execute query
		ResultSet rs = null;
		Connection conn = getConnection();
		try {
			rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(joinQuery);
			courses = buildCoursesList(rs);
			if (onlyAvailable) {
				List<Course> availableCourses = new LinkedList<Course>();
				for (Course course : courses) {
					try {
						if (!clashes(user, course.getId(), null)) {
							availableCourses.add(course);
						}
					} catch (Exception e) {
						Log.err("trouble checking clashing of course");
					}
				}
				courses = availableCourses;

			}
		} catch (Exception e) {
			Log.err("CourseHandler[getCourseByTags]: trouble getting courses");
		} finally {
			closeConnection(rs);
		}
		return courses;
	}
	/*
	 * Delete Course - delete course with id
	 */
	public void deleteCourse(int id) throws MyException {
		// check id
		if (id < 0)
			throw new MyException(CourseException.BAD_PARAMS);

		// delete course tags
		TagHandler th = new TagHandler();
		try {
			th.removeTagsFromCourse(id);
		} catch (Exception e) {
			throw new DBException(Tables.Tags.dbName, "DELETE COURSE TAGS",
					e.getMessage(), DBException.DEFAULT);
		}

		// delete course sessions
		SessionHandler sh = new SessionHandler();
		try {
			sh.deleteSessionsOfCourse(id);
		} catch (Exception e) {
		}

		// delete user courses
		ResultSet rs = null;
		UserHandler uh = new UserHandler();
		try {
			String where = Tables.CourseToUser.Columns.COURSE_ID + " = "
					+ String.valueOf(id);
			rs = uh.getCourseToUserResultSet(where);
			if (rs.first()) {
				rs.deleteRow();
				while (rs.next()) {
					rs.deleteRow();
				}
			}
		} catch (Exception e) {

		} finally {
			this.closeConnection(rs);
		}

		// get course
		String where = new String(Tables.Course.Columns.ID + " = " + id);
		try {
			rs = this.getCourseResultSet(where, null);
		} catch (SQLException e1) {
		}
		if (this.isEmptyResultSet(rs, Tables.Course.dbName)) {
			this.closeConnection(rs);
			throw new DBException(Tables.Course.dbName, null,
					"Value not found", DBException.VALUE_NOT_FOUND);
		}

		// delete course
		try {
			rs.first();
			rs.deleteRow();
			this.closeConnection(rs);
		} catch (SQLException e) {
			throw new DBException(Tables.Course.dbName, "DELETE COURSE",
					e.getMessage(), DBException.DEFAULT);
		} finally {
			this.closeConnection(rs);
		}
	}

	/*
	 * Change status of courseToUser
	 */
	public void changeUserCourseStatus(int courseId, User user, int status)
			throws MyException {
		Object[][] data = null;
		switch (status) {
			case -1 :
				try {
					removeCourseFromUser(courseId, user, null);
				} catch (SQLException e) {
				}
				break;
			case 1 :
				registerUserToCourse(courseId, user, status);
				break;
			case 0 :
				data = this.createAddCourseToUserData(courseId, user, status);
				try {
					super.insert(data, Tables.SCHEMA + "."
							+ Tables.CourseToUser.dbName, null);
				} catch (SQLException e) {
				}
				break;
			default :
				throw new MyException();
		}
	}

	/*
	 * get status of courseToUser 1 - registered 0 - in wishlist -1 - no
	 * connection
	 */
	public int getStatusOfCourseUser(Course course, User user)
			throws MyException {
		// create condition
		String where = "`" + Tables.CourseToUser.Columns.COURSE_ID + "`="
				+ String.valueOf(course.getId()) + " AND `"
				+ Tables.CourseToUser.Columns.USER_ID + "`="
				+ String.valueOf(user.getId());

		// get status
		ResultSet rs = null;
		try {
			rs = super.fetch(Tables.SCHEMA + "." + Tables.CourseToUser.dbName,
					where, null);
		} catch (SQLException e1) {
		}
		int status = -1;
		try {
			rs.next();
			status = rs.getInt(Tables.CourseToUser.Columns.IS_REGISTERED);
		} catch (SQLException e) {
		}
		return status;
	}

	/* ******* */
	/* Helpers */
	/* ******* */

	/*
	 * Do Fetch Course - do the fetching of the course (condition: where)
	 */
	private Course doFetchCourse(String where, Connection conn)
			throws DBException, SQLException {
		boolean gotConnection = (conn != null);
		ResultSet rs = null;
		Course course = null;
		rs = getCourseResultSet(where, conn);
		course = resultSetToCourse(rs, false, true);
		if (!gotConnection) {
			closeConnection(rs);
		} else {
			try {
				rs.close();
			} catch (Exception e) {
				Log.err("couldn't close resultset");
			}
		}
		return course;
	}

	/*
	 * Remove course from user
	 */
	private void removeCourseFromUser(int id, User user, Connection conn)
			throws SQLException {
		boolean gotConnection = (conn != null);
		// create conditions
		String where = "`" + Tables.CourseToUser.Columns.COURSE_ID + "`="
				+ String.valueOf(id) + " AND `"
				+ Tables.CourseToUser.Columns.USER_ID + "`="
				+ String.valueOf(user.getId());
		String[] cols = new String[1];
		cols[0] = Tables.CourseToUser.Columns.IS_REGISTERED;
		String[] newVals = new String[1];
		newVals[0] = "1";

		// remove course from user
		ResultSet rs = null;
		try {
			rs = super.fetch(Tables.SCHEMA + "." + Tables.CourseToUser.dbName,
					where, conn);
			rs.next();
			rs.deleteRow();
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			Log.err("trouble removing course from user");
		} finally {
			if (!gotConnection) {
				closeConnection(rs);
			} else {
				try {
					rs.close();
				} catch (Exception e) {
					Log.err("couldn't close resultset");
				}
			}
		}
	}

	/*
	 * Build Courses List - build courses list from ResultSet
	 */
	private List<Course> buildCoursesList(ResultSet rs) throws DBException {
		ArrayList<Course> courses = new ArrayList<Course>();

		// extract all items from ResultSet
		try {
			rs.first();
			while (!rs.isAfterLast()) {
				Course course = resultSetToCourse(rs, false, false);
				course.setTaken(getTakenSpots(course.getId(), null));
				courses.add(course);
				rs.next();
			}
		} catch (SQLException e) {
			throw new DBException(Tables.User.dbName, "ON BUILD USERS ARRAY",
					e.getMessage(), DBException.DEFAULT);
		} finally {
			this.closeConnection(rs);
		}
		return courses;
	}

	/*
	 * Create Add Course Data - create user data for adding
	 */
	private Object[][] createAddCourseData(Course course) {
		Object[][] data = new Object[6][2];
		// create data array
		data[0][COL_NAME] = Tables.Course.Columns.NAME;
		data[0][COL_VAL] = course.getName();
		data[1][COL_NAME] = Tables.Course.Columns.DESCRIPTION;
		data[1][COL_VAL] = course.getDescription();
		data[2][COL_NAME] = Tables.Course.Columns.CREDITS;
		data[2][COL_VAL] = course.getCredits();
		data[3][COL_NAME] = Tables.Course.Columns.CAPACITY;
		data[3][COL_VAL] = course.getCapacity();
		data[4][COL_NAME] = Tables.Course.Columns.GROUP_ID;
		data[4][COL_VAL] = course.getGroupId();
		data[5][COL_NAME] = Tables.Course.Columns.CREATOR_ID;
		data[5][COL_VAL] = course.getCreator();
		return data;
	}

	/*
	 * create add courseToUser data
	 */
	private Object[][] createAddCourseToUserData(int id, User user,
			int isRegister) {
		Object[][] data = new Object[9][2];
		// create data array
		data[0][COL_NAME] = Tables.CourseToUser.Columns.COURSE_ID;
		data[0][COL_VAL] = id;
		data[1][COL_NAME] = Tables.CourseToUser.Columns.USER_ID;
		data[1][COL_VAL] = user.getId();
		data[2][COL_NAME] = Tables.CourseToUser.Columns.IS_REGISTERED;
		data[2][COL_VAL] = isRegister;
		return data;
	}

	/*
	 * ResultSet to Course - convert ResultSet to Course
	 */
	private Course resultSetToCourse(ResultSet rs, boolean isClose,
			boolean isNeedNext) throws DBException, SQLException {
		// convert result to User type and close connection
		Course course = null;
		if (isNeedNext)
			rs.next();
		course = new Course(rs.getInt(Tables.Course.dbName + "."
				+ Tables.Course.Columns.ID), rs.getString(Tables.Course.dbName
				+ "." + Tables.Course.Columns.NAME),
				rs.getString(Tables.Course.Columns.DESCRIPTION),
				rs.getInt(Tables.Course.Columns.CREDITS),
				rs.getInt(Tables.Course.Columns.CAPACITY),
				rs.getInt(Tables.Course.Columns.GROUP_ID),
				rs.getInt(Tables.Course.Columns.CREATOR_ID));
		if (isClose)
			this.closeConnection(rs);
		return course;
	}

	/*
	 * Get Course ResultSet - get course result set (condition: where)
	 */
	private ResultSet getCourseResultSet(String where, Connection conn)
			throws DBException, SQLException {
		boolean gotConnection = (conn != null);
		ResultSet rs = super.fetch(Tables.SCHEMA + "." + Tables.Course.dbName,
				where, conn);
		if (this.isEmptyResultSet(rs, Tables.SCHEMA + "."
				+ Tables.Course.dbName)) {
			if (!gotConnection) {
				closeConnection(rs);
			} else {
				try {
					rs.close();
				} catch (Exception e) {
					Log.err("couldn't close result set");
				}
			}
			throw new DBException(Tables.Course.dbName, null,
					"Value not found", DBException.VALUE_NOT_FOUND);
		}
		return rs;
	}

	/*
	 * Get Taken Spots - returns amount of spots taken
	 */
	private int getTakenSpots(int id, Connection conn) throws SQLException {
		boolean gotConnection = (conn != null);
		String query = "SELECT COUNT(*) AS `c` FROM "
				+ Tables.CourseToUser.fullDBName + " WHERE `"
				+ Tables.CourseToUser.Columns.COURSE_ID + "` = "
				+ String.valueOf(id) + " AND `"
				+ Tables.CourseToUser.Columns.IS_REGISTERED + "` = 1";
		ResultSet rs = null;
		int count = 0;
		try {
			if (!gotConnection) {
				conn = getConnection();
			}
			rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(query);
			rs.first();
			count = rs.getInt("c");
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			Log.err("CourseHandler[getCourseByTags]: trouble getting courses");
		} finally {
			if (!gotConnection) {
				closeConnection(rs);
			} else {
				try {
					rs.close();
				} catch (Exception e) {
					Log.err("couldn't close resultset");
				}
			}
		}
		return count;

	}

	/*
	 * Clashes - check if course clashes with user's courses
	 */
	public boolean clashes(User user, int courseId, Connection conn)
			throws SQLException {
		Course course = null;
		int flag = 0;
		try {
			course = fetchCourse(courseId, conn);
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			Log.err("Course doesn't exist");
			return true;
		}

		// check if is in more than three groups already (besides this one)
		int groupId = course.getGroupId();
		String checkIfUserIsEmpty = "SELECT COUNT(*) AS `c` FROM "
				+ Tables.Course.fullDBName + ","
				+ Tables.CourseToUser.fullDBName + " WHERE "
				+ Tables.Course.fullDBName + ".`" + Tables.Course.Columns.ID
				+ "`=" + Tables.CourseToUser.fullDBName + ".`"
				+ Tables.CourseToUser.Columns.COURSE_ID + "` AND `"
				+ Tables.Course.Columns.GROUP_ID + "`!="
				+ String.valueOf(groupId) + " AND `"
				+ Tables.CourseToUser.Columns.USER_ID + "`="
				+ String.valueOf(user.getId()) + " AND "
				+ Tables.CourseToUser.fullDBName + ".`"
				+ Tables.CourseToUser.Columns.IS_REGISTERED + "`=1";
		String checkNumberOfGroups = "SELECT COUNT(*) FROM ("
				+ checkIfUserIsEmpty + " GROUP BY " + Tables.Course.fullDBName
				+ ".`" + Tables.Course.Columns.GROUP_ID + "`) AS `d`";

		// execute query
		ResultSet rs = null;
		if (conn == null) {
			try {
				conn = getConnection();
			} catch (DBException e1) {
				Log.err("CourseHandler[checkSession]: trouble getting connection");
			}
		}
		try {
			rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE)
					.executeQuery(checkIfUserIsEmpty);
			if (!rs.first()) {
				if (flag == 0) {
					flag = -1;
				}
			}
			rs.close();
			rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(
					checkNumberOfGroups);
			rs.first();
			if (rs.getInt(1) > 2) {
				if (flag == 0) {
					flag = 1;
				}
			}
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			Log.err("CourseHandler[checkSession]: trouble getting query");
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				Log.err("Couldn't close connection");
			}
		}

		SessionHandler sh = new SessionHandler();
		List<Session> sessions = null;
		try {
			sessions = sh.fetchAllCourseSessions(course, conn);
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			Log.err("course has no sessions");
			if (flag == 0) {
				flag = -1;
			}
		}
		for (Session session : sessions) {
			try {
				if (!sh.sessionAvailableToUser(session, user, conn)) {
					if (flag == 0) {
						flag = 1;
					}
				}
			} catch (SQLException e) {
				throw e;
			} catch (Exception e) {
				Log.err("session clashes");
				if (flag == 0) {
					flag = 1;
				}
			}
		}
		return (flag == 1);
	}

	/*
	 * Register User to course
	 */
	private void registerUserToCourse(int courseId, User user, int status)
			throws MyException, DBException {
		Object[][] data;
		// create connection

		/*
		 * the next segment of code is syncronized by locking the appropriate
		 * sql query rows so as not to hurt concurency
		 */
		Connection conn = null;
		try {
			conn = getConnection();
		} catch (Exception e) {
			Log.err("Couldn't get connection");
			return;
		}
		try {
			conn.setTransactionIsolation(1);
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			Log.err("Couldn't lock, exited...");
			return;
		}
		try {
			// check if clashes
			if (clashes(user, courseId, conn)) {
				throw new MyException();
			}

			// remove if exists
			try {
				removeCourseFromUser(courseId, user, conn);
			} catch (Exception ignore) {
				// since if it didn't succeed and if it did we don't care
			}

			// check that course is not full
			Course course = fetchCourse(courseId, conn);
			if (course.getCapacity() - course.getTaken() <= 0) {
				try {
					conn.close();
				} catch (Exception e) {
					Log.err("couldn't close connection");
				}
				throw new MyException();
			}
			data = this.createAddCourseToUserData(courseId, user, status);
			super.insert(data,
					Tables.SCHEMA + "." + Tables.CourseToUser.dbName, conn);// insert
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				Log.err("failed to rollback, we are all doomed");
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				Log.err("couldn't close connection");
			}
		}
	}

	/*
	 * Check if all sessions are available
	 */
	private boolean SessionsAreAvailable(List<Session> slots, Course course,
			Connection conn) {
		SessionHandler sh = new SessionHandler();
		boolean flag = true;
		// check if sessions clash with themselves
		for (Session session1 : slots) {
			for (Session session2 : slots) {
				if ((session1 != session2)
						&& (!((session1.getEndSlot() < session2.getStartSlot()) || ((session1
								.getStartSlot() > session2.getEndSlot()))))) {
					return false;
				}
			}
		}

		// check if sessions are available
		for (Session session : slots) {
			try {
				if (!sh.sessionAvailable(course, session, conn)) {
					flag = false;
					break;
				}

			} catch (Exception e) {
				return false;
			}
		}
		return flag;
	}

}
